package com.erp.app.catalog.itemMaster.dao;

import oracle.sql.BLOB;
import java.sql.Blob;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import org.apache.commons.beanutils.MethodUtils;

import com.erp.app.catalog.itemMaster.bizLogic.ItemMasterGroupCommonUtil;

import com.erp.app.catalog.itemMaster.criteria.ItemMasterCriteria;
import com.erp.app.catalog.itemMaster.criteria.ItemMasterDtlCriteria;
import com.erp.app.catalog.itemMaster.criteria.ItemMasterPicCriteria;
import com.erp.app.catalog.itemMaster.criteria.ItemMasterPriceCriteria;
import com.erp.app.catalog.itemMaster.criteria.ItemMasterRelateItemCriteria;

import com.erp.app.catalog.itemMaster.vo.ItemMasterDtlVO;
import com.erp.app.catalog.itemMaster.vo.ItemMasterPicVO;
import com.erp.app.catalog.itemMaster.vo.ItemMasterPriceVO;
import com.erp.app.catalog.itemMaster.vo.ItemMasterRelateItemVO;
import com.erp.app.catalog.itemMaster.vo.ItemMasterVO;

import com.erp.app.common.ProjectConstants;

import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBlob;
import com.erp.framework.util.CommonUtil;
import com.erp.framework.util.logger.Logger;

public class ItemMasterGroupCommonDao {
	ItemMasterGroupCommonUtil util = new ItemMasterGroupCommonUtil();
	
	public static String getItemMasterSqlQuery(SimpleBaseSrchCriteria obj) {
		ItemMasterCriteria criteria = (ItemMasterCriteria)obj;
		String sql = "";
		
		String whereClause = itemMasterToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "";
		sql = " SELECT a.fabric_code, a.company_code, e.attachment, "
			+ " Sf_Get_Browser_Column_Desc (a.company_code, a.fabric_code, a.node_id, 1) content, "
			+ " Sf_Get_Browser_Column_Desc (a.company_code, a.fabric_code, a.node_id, 2) description, "
			+ " a.create_user, a.create_date, a.update_user, a.update_date "
			+ " FROM T_FABRIC_HDR a, T_FABRIC_IMAGE b, T_IMAGE c, T_IMAGE_LEVEL d, T_ATTACHMENT e "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		Logger.sql("getItemMasterSqlQuery : "+sql);
		return sql;
	}
	public static SimpleBaseVo getItemMasterSearchResultSetValues(ResultSet rs) throws SQLException {
		
		ItemMasterVO vo = new ItemMasterVO();
		vo.setFabricCode(rs.getString("fabric_code"));
		vo.setCompanyCode(rs.getString("company_code"));		
		
		vo.setAttachment(CustomBlob.customize((BLOB)rs.getBlob("attachment")));
		vo.setContent(rs.getString("content"));
		vo.setItemDesc(rs.getString("description"));
		
		vo.setCreateUser(rs.getString("create_user"));
		vo.setCreateDate(rs.getTimestamp("create_date"));
		vo.setUpdateUser(rs.getString("update_user"));
		vo.setUpdateDate(rs.getTimestamp("update_date"));
		
		return vo;
	}
	
	public static String itemMasterToWhereClause(SimpleBaseSrchCriteria obj) {
		ItemMasterCriteria criteria = (ItemMasterCriteria)obj;
		StringBuffer whereClause = new StringBuffer();

		whereClause.append(" WHERE a.company_code = b.company_code(+) "
				+ " AND a.fabric_code = b.fabric_code(+) "
				+ " AND (b.line_no IS NULL OR b.line_no=(SELECT MIN(a1.line_no) "
													+ " FROM  T_FABRIC_IMAGE a1 " 
													+ " WHERE a1.company_code=a.company_code "
													  + " AND a1.fabric_code=a.fabric_code))"
		        + " AND b.company_code=c.company_code(+) "
		        + " AND b.image_id=c.image_id(+) "
		        + " AND c.company_code=d.company_code(+) "
		        + " AND c.image_id=d.image_id(+) "
		        + " AND (d.level_id(+)=2) "
		        + " AND d.company_code=e.company_code(+) "
		        + " AND d.attachment_line_no=e.line_no(+) ");		

		if (!CommonUtil.isEmpty(criteria.getSrchCompanyCode())) {          
          whereClause.append(" AND a.company_code=(" 
        		  + criteria.getSrchCompanyCode().trim().toUpperCase() + ") ");          
		}
		if (!CommonUtil.isEmpty(criteria.getUserCode())){
		  whereClause.append("AND NOT EXISTS ( SELECT 1 "
				  	+ " FROM  T_FABRIC_CATALOG a4, T_CATALOG_SECURITY_DTL a5 "
				  	+ " WHERE a.company_code=a4.company_code "
				  	+   " AND a.fabric_code=a4.fabric_code "
				  	+   " AND a4.company_code=a5.company_code "
				  	+   " AND a4.node_id=a5.node_id "
				  	+   " AND a5.user_code='"+criteria.getUserCode().trim().toUpperCase()+"')");
		}
		if (!CommonUtil.isEmpty(criteria.getSrchText())){
		  whereClause.append(" AND (A.FABRIC_CODE LIKE '%"+criteria.getSrchText()+"%' "
            + " OR UPPER(A.FABRIC_NAME) LIKE '%"+criteria.getSrchText()+"%' "
            + " OR EXISTS (SELECT 1 "
                + " FROM  T_FABRIC_CATALOG_DTL A1, "
                +       " T_CATALOG_DTL A2 "
                + " WHERE A1.COMPANY_CODE = A.COMPANY_CODE "
                +   " AND A1.FABRIC_CODE = A.FABRIC_CODE "
                +   " AND A1.COMPANY_CODE = A2.COMPANY_CODE "
                +   " AND A1.DTL_ID = A2.DTL_ID "
                +   " AND ((A2.DTL_TYPE <> 'W' "
                +       " AND UPPER(A1.DTL_VALUE) LIKE '%"+criteria.getSrchText()+"%') "
                +       " OR (A2.DTL_TYPE = 'W' "
                +           " AND EXISTS (SELECT 1 "
                +	             " FROM  T_CATALOG_OPTION_DTL B1 "
                +                " WHERE B1.COMPANY_CODE = A1.COMPANY_CODE "
                +                  " AND B1.REF_MASTER_CODE = A2.OPTION_CODE "
                +                  " AND B1.REF_CODE = A1.DTL_VALUE "
                +                  " AND UPPER(B1.REF_DESC) LIKE '%"+criteria.getSrchText()+"%')))) "
            + " OR EXISTS (SELECT 1 "
                   + " FROM  T_FABRIC_CATALOG A1, "
                   +      "  T_CATALOG A2 "
                   + " WHERE A1.COMPANY_CODE = A.COMPANY_CODE "
                   +   " AND A1.FABRIC_CODE = A.FABRIC_CODE "
                   +   " AND A1.COMPANY_CODE = A2.COMPANY_CODE "
                   +   " AND A1.NODE_ID = A2.NODE_ID "
                   +   " AND UPPER(A2.DESCRIPTION) LIKE '%"+criteria.getSrchText()+"%')) ");
		}
		
		if (!CommonUtil.isEmpty(criteria.getNodeIdList())){
		  whereClause.append(" AND EXISTS (SELECT 1 FROM t_fabric_catalog a4 "
		    + " WHERE a.company_code = a4.company_code AND a.fabric_code = a4.fabric_code "
            + " AND a4.node_id in("+criteria.getNodeIdList()+"))");
		}
		return whereClause.toString();    	
	}
	
	public static String getItemMasterDtlSqlQuery(SimpleBaseSrchCriteria obj) {
		ItemMasterDtlCriteria criteria = (ItemMasterDtlCriteria)obj;
		String sql = "";
		
		String whereClause = itemMasterDtlToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "";

		sql = " SELECT a.company_code, a.fabric_code" 
		  + ", DECODE (NVL (a.recursive_line_no, -1),-1,a.dtl_id,"
          +     		"Sf_Get_Recdtl_Id (a.company_code, a.fabric_code, a.dtl_id)) dtl_id" 
          +	", DECODE (NVL (a.recursive_line_no, -1),-1, DECODE (b.dtl_type,'W'," 
          +          	" (SELECT a1.ref_desc FROM T_CATALOG_OPTION_DTL a1 " 
          +          		               " WHERE a1.company_code = a.company_code "
          +          		                 " AND a1.ref_master_code = b.option_code "
          +          		                 " AND a1.ref_code = a.dtl_value),"
          +             " a.dtl_value), Sf_Get_Recdtl_Desc (a.company_code, a.fabric_code, a.dtl_id)) dtl_value" 
          + ", a.status, b.dtl_type, b.dtl_name, b.uom, b.option_code "
          + " FROM T_FABRIC_CATALOG_DTL a, T_CATALOG_DTL b "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		Logger.log("getItemMasterDtlSqlQuery = " + sql);
		
		return sql;
	}
	public static SimpleBaseVo getItemMasterDtlSearchResultSetValues(ResultSet rs) throws SQLException {		
		ItemMasterDtlVO vo = new ItemMasterDtlVO();		
		
		vo.setCompanyCode(rs.getString("company_code"));
		vo.setFabricCode(rs.getString("fabric_code"));
		vo.setDtlId(rs.getString("dtl_id"));
		vo.setDtlValue(rs.getString("dtl_value"));
		vo.setDtlType(rs.getString("dtl_type"));
		vo.setDtlName(rs.getString("dtl_name"));
		vo.setUom(rs.getString("uom"));
		vo.setOptionCode(rs.getString("option_code"));
		vo.setStatus(rs.getString("status"));
		
		return vo;
	}	
	public static String itemMasterDtlToWhereClause(SimpleBaseSrchCriteria obj) {
		ItemMasterDtlCriteria criteria = (ItemMasterDtlCriteria)obj;
		StringBuffer whereClause = new StringBuffer();		
		
		whereClause.append(" WHERE a.company_code = b.company_code AND a.dtl_id = b.dtl_id "
				+ " AND NOT EXISTS (SELECT 1 FROM T_FABRIC_CATALOG_DTL a1 "
				+ " WHERE a1.company_code = a.company_code AND a1.fabric_code = a.fabric_code "
				+   " AND a1.recursive_line_no = a.line_no) ");
		
		if(!CommonUtil.isEmpty(criteria.getCompanyCode())){
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" a.company_code = '" + criteria.getCompanyCode().trim().toUpperCase() + "' ");
		}
		if(!CommonUtil.isEmpty(criteria.getFabricCode())){
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" a.fabric_code = '" + criteria.getFabricCode().trim().toUpperCase() + "' ");
		}
		if(!CommonUtil.isEmpty(criteria.getUserId())){
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" NOT EXISTS (SELECT 1 FROM T_FABRIC_HDR a1, T_CATALOG_SECURITY_FIELD a2 "
                + " WHERE a1.company_code = a.company_code AND a1.fabric_code = a.fabric_code "
                +  " AND a1.company_code = a2.company_code AND a1.node_id = a2.node_id "
                +  " AND a2.dtl_id = a.dtl_id AND a2.user_code = '" 
                + criteria.getUserId().trim().toUpperCase() + "') ");
		}
		
		return whereClause.toString();    	
	}
	
	public static String getItemMasterPicSqlQuery(SimpleBaseSrchCriteria obj){
		ItemMasterPicCriteria criteria = (ItemMasterPicCriteria)obj;
		String sql = "";
		
		String whereClause = itemMasterPicToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "";	
		
		sql = " SELECT a.company_code, a.fabric_code, a.line_no, a.image_id " 
			+	 ", c1.ATTACHMENT_LINE_NO lv1_attach_line_no, d1.ATTACHMENT_DESC lv1_attach_desc " 
			+ 	 ", d1.CONTENT_TYPE lv1_attach_content_type, d1.ATTACHMENT lv1_attachment "
			+	 ", c2.ATTACHMENT_LINE_NO lv2_attach_line_no, d2.ATTACHMENT_DESC lv2_attach_desc " 
			+ 	 ", d2.CONTENT_TYPE lv2_attach_content_type, d2.ATTACHMENT lv2_attachment "
			+ " FROM t_fabric_image a, t_image b "
			+     ", t_image_level c1, t_attachment d1 "
			+	  ", t_image_level c2, t_attachment d2 "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		
		Logger.log("getItemMasterPicSqlQuery"+sql);
		
		return sql;
	}	
	public static SimpleBaseVo getItemMasterPicSearchResultSetValues(ResultSet rs)
			throws SQLException {		
		ItemMasterPicVO vo = new ItemMasterPicVO();		
		
		vo.setCompanyCode(rs.getString("company_code"));
		vo.setFabricCode(rs.getString("fabric_code"));
		vo.setLineNo(CommonUtil.bigDecimalToStr(rs.getBigDecimal("line_no")));
		vo.setImageId(CommonUtil.bigDecimalToStr(rs.getBigDecimal("image_id")));
		
		vo.setLv1AttachLineNo(CommonUtil.bigDecimalToStr(rs.getBigDecimal("lv1_attach_line_no")));
		vo.setLv1AttachContentType(rs.getString("lv1_attach_content_type"));
		vo.setLv1AttachDesc(rs.getString("lv1_attach_desc"));
		vo.setLv1Attachment(CustomBlob.customize((BLOB)rs.getBlob("lv1_attachment")));
		
		vo.setLv2AttachLineNo(CommonUtil.bigDecimalToStr(rs.getBigDecimal("lv2_attach_line_no")));
		vo.setLv2AttachContentType(rs.getString("lv2_attach_content_type"));
		vo.setLv2AttachDesc(rs.getString("lv2_attach_desc"));
		vo.setLv2Attachment(CustomBlob.customize((BLOB)rs.getBlob("lv2_attachment")));
		
		return vo;
	}	
	public static String itemMasterPicToWhereClause(SimpleBaseSrchCriteria obj){
		ItemMasterPicCriteria criteria = (ItemMasterPicCriteria)obj;
		StringBuffer whereClause = new StringBuffer();
		
		whereClause.append(" WHERE a.company_code = b.company_code(+) AND a.image_id = b.image_id(+) "
		  + " AND b.company_code = c1.company_code(+) AND b.image_id = c1.image_id(+) AND c1.level_id(+) = 1 "
		  + " AND c1.company_code = d1.company_code(+) AND c1.attachment_line_no = d1.line_no(+) "
		  + " AND b.company_code = c2.company_code(+) AND b.image_id = c2.image_id(+) AND c2.level_id(+) = 2 "
		  + " AND c2.company_code = d2.company_code(+) AND c2.attachment_line_no = d2.line_no(+) "
		);
		if (!CommonUtil.isEmpty(criteria.getCompanyCode())) {
          whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
          whereClause.append(" a.company_code='"+criteria.getCompanyCode().trim().toUpperCase()+"' ");
        }
		if (!CommonUtil.isEmpty(criteria.getFabricCode())) {
	      whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
	      whereClause.append(" a.fabric_code='"+criteria.getFabricCode().trim().toUpperCase()+"' ");
	    }
		if (!CommonUtil.isEmpty(criteria.getLineNo())) {
		  whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
		  whereClause.append(" a.line_no='"+criteria.getLineNo().trim().toUpperCase()+"' ");
		}
		
		return whereClause.toString();
	}
	
	public static String getItemMasterPriceSqlQuery(SimpleBaseSrchCriteria obj) {
		ItemMasterPriceCriteria criteria = (ItemMasterPriceCriteria)obj;
		String sql = "";
		
		String whereClause = itemMasterPriceToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "";
		
		sql = " SELECT c.ITEM_ID, a.CUST_ITEM_CODE, a.RETAIL_PRICE, a.SELLING_PRICE, b.CUST_CODE, "
        	+ "	b.FROM_QTY, b.TO_QTY, b.UNIT_PRICE, b.CCY, b.VALIDATE_DATE "
        	+ " FROM T_ITEM_CUST a, T_ITEM_CUST_PRICE b, T_ITEM_HEADER c "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		
		Logger.log(sql);
		
		return sql;
	}
	public static SimpleBaseVo getItemMasterPriceSearchResultSetValues(ResultSet rs)
			throws SQLException {		
		ItemMasterPriceVO vo = new ItemMasterPriceVO();
		
		vo.setItemId(rs.getString("ITEM_ID"));
		vo.setCustItemCode(rs.getString("CUST_ITEM_CODE"));
		vo.setRetailPrice(rs.getBigDecimal("RETAIL_PRICE"));
		vo.setSellingPrice(rs.getBigDecimal("SELLING_PRICE"));
		vo.setCustCode(rs.getString("CUST_CODE"));
		vo.setFromQty(rs.getBigDecimal("FROM_QTY"));
		vo.setToQty(rs.getBigDecimal("TO_QTY"));
		vo.setUnitPrice(rs.getBigDecimal("UNIT_PRICE"));
		vo.setCcy(rs.getString("CCY"));
		vo.setValidateDate(rs.getDate("VALIDATE_DATE"));	
		
		return vo;
	}
	
	public static String itemMasterPriceToWhereClause(SimpleBaseSrchCriteria obj) {
		ItemMasterPriceCriteria criteria = (ItemMasterPriceCriteria)obj;
		StringBuffer whereClause = new StringBuffer();
		whereClause.append("WHERE a.company_code = b.company_code(+) "
    			+ "	AND a.item_code = b.item_code(+) "
    			+ "	AND a.line_no = b.cust_line_no(+) "
    			+ "	AND a.company_code = c.create_company_code "
    			+ "	AND a.item_code = c.acticle_number ");
		
		if (!CommonUtil.isEmpty(criteria.getItemId())) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" c.ITEM_ID IN (" + criteria.getItemId().trim().toUpperCase() + ") ");
        }
		
		return whereClause.toString();
	}
	public static String getItemMasterRelateItemSqlQuery(SimpleBaseSrchCriteria obj) {
		ItemMasterRelateItemCriteria criteria = (ItemMasterRelateItemCriteria)obj;
		String sql = "";
		
		String whereClause = itemMasterRelateItemToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "";
		
		sql = " SELECT ITEM_ID, CHILD_ID, CREATE_COMPANY_CODE, AUDIT_ID, ACTIVE FROM T_ITEM_CHILDREN "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		
		Logger.log(sql);
		
		return sql;
	}
	public static SimpleBaseVo getItemMasterRelateItemSearchResultSetValues(ResultSet rs) throws SQLException {		
		ItemMasterRelateItemVO vo = new ItemMasterRelateItemVO();
		
		vo.setItemId(CommonUtil.isEmpty(rs.getObject("ITEM_ID"))?"":rs.getBigDecimal("ITEM_ID").toString());
		vo.setChildId(CommonUtil.isEmpty(rs.getObject("CHILD_ID"))?"":rs.getBigDecimal("CHILD_ID").toString());
		vo.setCreateCompanyCode(rs.getString("CREATE_COMPANY_CODE"));		
		vo.setAuditId(CommonUtil.isEmpty(rs.getObject("AUDIT_ID"))?"":rs.getBigDecimal("AUDIT_ID").toString());
		vo.setActive(CommonUtil.isEmpty(rs.getObject("ACTIVE"))?"":rs.getBigDecimal("ACTIVE").toString());
		
		return vo;
	}
	
	public static String itemMasterRelateItemToWhereClause(SimpleBaseSrchCriteria obj) {
		ItemMasterRelateItemCriteria criteria = (ItemMasterRelateItemCriteria)obj;
		StringBuffer whereClause = new StringBuffer();		
		
		if (!CommonUtil.isEmpty(criteria.getItemId())) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" ITEM_ID = '" + criteria.getItemId().trim().toUpperCase() + "' ");
        }
		if (!CommonUtil.isEmpty(criteria.getChildId())) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append(" CHILD_ID = '" + criteria.getChildId().trim().toUpperCase() + "' ");
        }		
		return whereClause.toString();
	}
	
	public Collection findByCriteria(Object obj, SimpleBaseVo model, Connection conn) throws FindRecordException {

        SimpleBaseSrchCriteria criteria = (SimpleBaseSrchCriteria) obj;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList rslt = new ArrayList();
        
        String criteriaClassName = criteria.getClass().toString();
        String getSqlQueryMethorName = "get"
			+ criteriaClassName.substring(criteriaClassName.lastIndexOf(".")+1,criteriaClassName.length()-8)
			+"SqlQuery";        
        
        String modelVoClassName = model.getClass().toString();        
        String getResultMethorName = "get"
        	+ modelVoClassName.substring(modelVoClassName.lastIndexOf(".")+1,modelVoClassName.length()-2)
        	+"SearchResultSetValues";
        
        try {
        	String query = (String)MethodUtils.invokeMethod(this,getSqlQueryMethorName,criteria);
            stmt = conn.createStatement();
            stmt.setMaxRows(ProjectConstants.DB_ROW_NUM);            
            
            rs = stmt.executeQuery(query);

            if (rs != null) {
                while (rs.next()) {
                	rslt.add(MethodUtils.invokeMethod(this,getResultMethorName,rs));
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
            throw new FindRecordException(e.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
        }
        return rslt;
    }
	public Collection findByCriteria(Object obj, Connection conn) throws FindRecordException {        
        
        SimpleBaseSrchCriteria criteria = (SimpleBaseSrchCriteria) obj;
        ArrayList rslt = new ArrayList();
  
        try{
        	rslt = (ArrayList)this.findByCriteria(criteria
        			,(SimpleBaseVo)Class.forName(criteria.getVOClassName()).newInstance(),conn);
        }catch(Exception e){
        	Logger.log(e.toString());
        }
        return rslt;
	}
}
